﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;  // for exe path of application
using MySql.Data.MySqlClient;     // for connection with database

namespace EventEntrance
{
    public class DataHelper
    {
        private MySqlConnection connection;
        //Constructor
        public DataHelper()
        {
            String connectionInfo = "server=athena01.fhict.local;" +
                             "database=dbi269964;" + // your database name
                             "user id=dbi269964;" + // your user id
                             "password=ztauJSorfR;" + // your password
                             "connect timeout=30;";
            connection = new MySqlConnection(connectionInfo);
        }
        //change the client status
        public int changeStatusClient(long clientId)
        {
            String visitorId = Convert.ToString(clientId);
            String sql = "UPDATE EVENT_STATUS SET HasEnteredEvent='y' WHERE Visitor_id='" + visitorId + "'";
            MySqlCommand command = new MySqlCommand(sql, connection);
            try
            {
                connection.Open();
                int nrOfRecordsChanged = command.ExecuteNonQuery();
                return nrOfRecordsChanged;
            }
            catch (MySqlException err)
            {
                MessageBox.Show("Error: " + err.ToString());
                return -1;
            }
            finally
            {
                connection.Close();
            }
        }
        //get clientId 
        public Client getClient(long clientId)
        {
            String visitorId = Convert.ToString(clientId);
            String sql = "SELECT v.Visitor_id,v.LastName,v.FirstName,e.MoneyDeposited,s.HasEnteredEvent FROM visitor v JOIN event_account e ON v.Visitor_Id=e.Visitor_id JOIN event_status s ON v.Visitor_Id=s.Visitor_Id WHERE v.Visitor_id='" + visitorId + "'";
            MySqlCommand command = new MySqlCommand(sql, connection);
            List<Client> temp;
            int nr = 0;
            temp = new List<Client>();

            try
            {
                connection.Open();
                MySqlDataReader reader = command.ExecuteReader();
                string name1, name2;
                double balance;
                long no;
                string status;
                while (reader.Read())
                {
                    no = Convert.ToInt64(reader["Visitor_id"]);
                    name1 = Convert.ToString(reader["LastName"]);
                    name2 = Convert.ToString(reader["FirstName"]);
                    balance = Convert.ToDouble(reader["MoneyDeposited"]);
                    status = Convert.ToString(reader["HasEnteredEvent"]);
                    temp.Add(new Client(no, name1, name2, balance,status));
                    nr++;
                }
            }
            catch(MySqlException err)
            {
                MessageBox.Show("Error: " + err.ToString());
            }
            finally
            {
                connection.Close();
            }
            if (nr > 0)
            {
                return temp[0];
            }
            else { return null; }
        }



        public string status { get; set; }
    }
}

